﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace PIBV
{
    public class PID_DrawingList : PID_BaseClass
    {
        public const string TABLE_NAME_ALL_TAGS = "PID_DrawingList_ALL_TAGS";
        public const string TABLE_NAME_ALL_TAGS_2 = "PID_DrawingList_ALL_TAGS_2";
        public const string TABLE_NAME_ALL_LOOPS = "PID_DrawingList_ALL_LOOPS";
        public const string TABLE_NAME_ALL_LOOPS_2 = "PID_DrawingList_ALL_LOOPS_2";
        public const string TABLE_NAME_ALL_PIDS = "PID_DrawingList_ALL_PIDS";
        public const string TABLE_NAME_ALL_PIDS_2 = "PID_DrawingList_ALL_PIDS_2";

        const string SQL_ALL_PIDS  = " SELECT DISTINCT L.PID FROM LOOP AS L INNER JOIN DRAWING AS D ON D.LoopID=L.LoopID WHERE (D.D_FILE_PDF IS NOT NULL OR D.D_FILE_PDF<>'' ) ";
        const string SQL_ALL_LOOPS = " SELECT DISTINCT L.LoopID, L.LoopName, L.PID, L.LoopDescription,L.LoopDescriptionLong,D.D_FILE_PDF,D.D_FILE_DGN FROM LOOP AS L INNER JOIN DRAWING AS D ON D.LoopID=L.LoopID  WHERE (D.D_FILE_PDF IS NOT NULL OR D.D_FILE_PDF<>'' ) ";
        const string SQL_ALL_TAGS = " SELECT DISTINCT T.TagName, L.LoopID, L.LoopName, L.PID, L.LoopDescription,L.LoopDescriptionLong,D.D_FILE_PDF,D.D_FILE_DGN FROM (TAG AS T INNER JOIN LOOP AS L ON L.LoopName = T.LoopName) INNER JOIN DRAWING AS D ON D.LoopID=L.LoopID  WHERE (D.D_FILE_PDF IS NOT NULL OR D.D_FILE_PDF<>'' ) ";
        
        //ctor
        public PID_DrawingList()
        {
        }

        //load records from database into memory
        public void InitializeData(bool bFillPID,bool bFillLOOPS) 
        {
            if(bFillPID)
                ExecuteQueryInToDataSet(SQL_ALL_PIDS, ds, TABLE_NAME_ALL_PIDS, true);

            if(bFillLOOPS)
                ExecuteQueryInToDataSet(SQL_ALL_LOOPS, ds, TABLE_NAME_ALL_LOOPS, true);
        }

        //get records by filter from cache
        public DataView GetDrawingsByPID(string filter)
        {
            DataView resultDataView = null;
            
            if (filter.Contains('*'))
            {
                filter = filter.Replace('*', '%');
                string sql = SQL_ALL_PIDS + String.Format(" AND PID LIKE '{0}' ", filter);
                ds = this.ExecuteQueryInToDataSet(sql, ds, TABLE_NAME_ALL_PIDS_2, true);
                resultDataView = new DataView(ds.Tables[TABLE_NAME_ALL_PIDS_2]);
            }
            else
            { 
                DataTable tbl = ds.Tables[TABLE_NAME_ALL_PIDS];
                resultDataView = new DataView(tbl, String.Format("PID LIKE '*{0}*'", filter), " PID ASC ", DataViewRowState.CurrentRows);
            }
            
            return resultDataView;
        }

        //get records by filter from cache
        public DataView GetDrawingsByLoopName(string filter)
        {
            DataView resultDataView = null;

            if (filter.Contains('*'))
            {
                filter = filter.Replace('*', '%');
                string sql = SQL_ALL_LOOPS + String.Format(" AND LoopName LIKE '{0}' ", filter);
                ds = this.ExecuteQueryInToDataSet(sql, ds, TABLE_NAME_ALL_LOOPS_2, true);
                resultDataView = new DataView(ds.Tables[TABLE_NAME_ALL_LOOPS_2]);
            }
            else
            {
                DataTable tbl = ds.Tables[TABLE_NAME_ALL_LOOPS];
                resultDataView = new DataView(tbl, String.Format("LoopName LIKE '%{0}%'", filter), " LoopName ASC ", DataViewRowState.CurrentRows);
            }

            return resultDataView;
        }

        //get records by filter from cache
        public DataView GetDrawingsByTagName(string filter)
        {
            DataView resultDataView = null;

            if (filter.Contains("*") || filter.Contains("%"))
            {
                filter = filter.Replace('*', '%');
            }
            else
            {
                filter = "%" + filter + "%";
            }
                string sql = SQL_ALL_TAGS + String.Format(" AND T.TagName LIKE '{0}' ", filter);
                ds = this.ExecuteQueryInToDataSet(sql, ds, TABLE_NAME_ALL_TAGS_2, true);
                resultDataView = new DataView(ds.Tables[TABLE_NAME_ALL_TAGS_2]);

            return resultDataView;
        }

        public LoadDrawingEventArgs GetFileName(string pid, string sTiffStoragePath)
        {
            LoadDrawingEventArgs result = null;
            
            DataTable tbl = ds.Tables[TABLE_NAME_ALL_LOOPS];// weak place !!! need check

            DataRow[] rows = tbl.Select(String.Format("PID = '{0}' ",pid));
            if (rows != null && rows.Length > 0)
            {
                string loopId =  rows[0]["LoopID"].ToString();
                string pdfFileName = System.IO.Path.GetFileName(rows[0]["D_FILE_PDF"].ToString());
                string dgnFileName = System.IO.Path.GetFileName(rows[0]["D_FILE_DGN"].ToString());
                string fileName = sTiffStoragePath + System.IO.Path.GetFileNameWithoutExtension(pdfFileName) + ".tif";

                result = new LoadDrawingEventArgs(new System.Windows.Point(), fileName, pdfFileName,dgnFileName, loopId, pid);
            }

            return result;
        }

        public LoadDrawingEventArgs GetFileName(long loopId, string sTiffStoragePath)
        {
            LoadDrawingEventArgs result = null;

            DataTable tbl = ds.Tables[TABLE_NAME_ALL_LOOPS];

            DataRow[] rows = tbl.Select(String.Format("LoopID = {0} ", loopId));
            if (rows != null && rows.Length > 0)
            {
                string pdfFileName = System.IO.Path.GetFileName(rows[0]["D_FILE_PDF"].ToString());
                string dgnFileName = System.IO.Path.GetFileName(rows[0]["D_FILE_DGN"].ToString());
                string fileName = sTiffStoragePath + System.IO.Path.GetFileNameWithoutExtension(pdfFileName) + ".tif";

                result = new LoadDrawingEventArgs(new System.Windows.Point(), fileName, pdfFileName, dgnFileName, Convert.ToString(loopId), "No data");
            }

            return result;
        }

    }
}
